#!/usr/bin/python
# -*- coding: UTF-8 -*-

from graphviz import Digraph, Graph
import re

# 最多显示的字段数量
MAX_NUM_OF_COLUMS = 16

ss = """
DROP TABLE IF EXISTS `dwd_dim_sku_info`;
CREATE EXTERNAL TABLE `dwd_dim_sku_info` (
`id` string COMMENT '商品 id',
`spu_id` string COMMENT 'spuid',
`price` double COMMENT '商品价格',
`sku_name` string COMMENT '商品名称',
`sku_desc` string COMMENT '商品描述',
`weight` double COMMENT '重量',
`tm_id` string COMMENT '品牌 id',
`tm_name` string COMMENT '品牌名称',
`category3_id` string COMMENT '三级分类 id',
`category2_id` string COMMENT '二级分类 id',
`category1_id` string COMMENT '一级分类 id',
`category3_name` string COMMENT '三级分类名称',
`category2_name` string COMMENT '二级分类名称',
`category1_name` string COMMENT '一级分类名称',
`spu_name` string COMMENT 'spu 名称',
`create_time` string COMMENT '创建时间'
)
COMMENT '商品维度表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_sku_info/'
tblproperties ("parquet.compression"="lzo");

insert overwrite table dwd_dim_sku_info partition(dt='2020-08-03')
select
    sku.id,
    sku.spu_id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.tm_id,
    ob.tm_name,
    sku.category3_id,
    c2.id category2_id,
    c1.id category1_id,
    c3.name category3_name,
    c2.name category2_name,
    c1.name category1_name,
    spu.spu_name,
    sku.create_time
from
(
select 
    a
    ,b
    ,c
from ods_sku_info 
where dt='2020-08-03'
) t1
join 
(
select 
    d
    ,e
    ,f
from ods_base_trademark 
where dt='2020-08-03'
) t2 on t1.a=t2.d
LEFT join
(
select 
    gg
    ,hh
    ,jj
from ods_spu_info 
where dt='2020-08-03'
)t3 on t1.id = t3.spu_id
FULL join
(
select 
    abc
    ,def
    ,gsa
from aa.ods_base_category3 
where dt='2020-08-03'
)t4
on t1.category3_id=t4.id
LEFT join
(
select 
    abcc
    ,defc
    ,gsac
from a.ods_base_category4 
where dt='2020-08-03'
)t5 
on t4.category2_id=t5.id
join
(
select 
    abceer
    ,defasd
    ,gsaqwe
from db.ods_base_category7 
where dt='2020-08-03'
)t6 on t5.category1_id=t6.id;
"""

s = Digraph('structs', filename='structs_revisited.gv',
            node_attr={'shape': 'record'})

# 字符串转HTML表格结构
def str2table_str(columns):
    # 限制字段个数
    const_columns = columns[0:MAX_NUM_OF_COLUMS]
    return ['<TR><TD COLSPAN="3">{}</TD></TR>'.format(column) for column in const_columns]

# 通过表结构渲染表格
def render_table(table_alias,table_name,table_str):
    if(table_alias):
        table_name = table_name +'({})'.format(table_alias)
    else:
        table_alias = table_name
    s.node(table_alias, fontname="Microsoft YaHei", label='''<
    <TABLE BORDER="0" CELLBORDER="1" CELLSPACING="0" CELLPADDING="4">
    <TR><TD COLSPAN="3"><B>{}</B></TD></TR>
      {}
    </TABLE>>'''.format(table_name,"\n".join(table_str)))

# 构建关系连线
def table_edge(table,target_table,label,arrowhead = 'None'):
    s.edge(table, target_table, label=label,arrowhead=arrowhead)


# ------ 处理目标表

target_t = re.findall(r'CREATE.*?TABLE.*?([\w\.]+?)`?\s*?\((.*?)\)\s*?COMMENT\s+',ss,re.S|re.M|re.I)[0]
target_tname = target_t[0]
target_t_columns = target_t[1]

target_t_columns.replace('\n','')
target_t_columns = re.split(r',\s*?',target_t_columns)
# 转换
target_t_columns = [re.sub(r'[\n|\'|`]','',colum) for colum in target_t_columns ]
print(target_tname,target_t_columns)

# 绘制目标表
# s = Graph('ER', filename='er.gv', engine='neato')
main_node_str = str2table_str(target_t_columns)

render_table('',target_tname,main_node_str)

# -----  处理目标表结束

# ----- 处理主表数据
# 拿到数据的sql
subtable = re.findall(r'insert.*?select(.*);',ss,re.S)
# 主表字段
main_table = re.findall(r'.*?from\s*?\(\s*?select\s*?(.*?)FROM\s+?([\w\.]+).*?\)\s*?(\w+)',subtable[0],re.S|re.I)
main_columns = main_table[0][0]  # 取第一个分区
main_columns = main_columns.replace('\n','')
main_columns = re.split(r'\s*?,\s*?',main_columns) # 分割
main_columns = [re.sub(r'[\s|\n|\'|`]','',colum) for colum in main_columns ] # 清洗
main_tname = main_table[0][1]      # 取表名
main_alias_name = main_table[0][2] #取别名

print(main_columns,main_tname,main_alias_name)
# 主表数据获取结束
# ---- 绘制主表
render_table(main_alias_name,main_tname,str2table_str(main_columns))


# ----- 获取子表数据
def get_table_struct(table_info):
    table_columns = table_info[0] # 字段
    table_name = table_info[1]  # 表名
    table_alias = table_info[2] # 表的别名
    table_res = table_info[3]   # 表的关联关系
    table_edge = re.findall(r'(\w+?)\.[\w =]+?(\w+?)\.',table_res,re.S|re.I|re.M)[0] # 表的连线
    table_columns = table_columns.replace('\n','')
    table_columns = re.split(r'\s*?,\s*?',table_columns) # 分割
    table_columns = [re.sub(r'[\s|\n|\'|`]','',colum) for colum in table_columns ] # 清洗
    return [table_columns,table_name,table_alias,table_res,table_edge]


# 处理所有的子表
sub_list = re.findall(r'join\s+?\(\s+?select\s+?(.*?)FROM\s+?([\w\.]+?)\s+?.*?\)\s*?(.*?)\s*?on\s+?([\w\.= ]+)'
                      ,subtable[0],re.S|re.I|re.M)

sub_table_list = []

for index in range(len(sub_list)):
    print(get_table_struct(sub_list[index]))
    sub_table_list.append(get_table_struct(sub_list[index]))

# 批量渲染
for sub_table in sub_table_list:
    render_table(re.sub('\s','',sub_table[2]),sub_table[1],str2table_str(sub_table[0]))

# 主表和目标表
s.edge(main_alias_name,target_tname) # arrowhead = 'None'

# 设置字体大小
s.attr(fontsize='20')

# 子表关联关系
for index in range(len(sub_table_list)):
    table_edge(sub_table_list[index][4][1],sub_table_list[index][4][0],label=sub_table_list[index][3])

s.view()
